"""initial migration

Revision ID: 510e96ad7bfb
Revises: 
Create Date: 2025-08-20 10:45:48.132086

"""
from typing import Sequence, Union

from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import mysql

# revision identifiers, used by Alembic.
revision: str = '510e96ad7bfb'
down_revision: Union[str, Sequence[str], None] = None
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    """Upgrade schema."""
    # ### commands auto generated by Alembic - please adjust! ###
    op.alter_column('alarm', 'CreateTime',
               existing_type=mysql.DATETIME(),
               server_default=sa.text('now()'),
               existing_comment='创建时间',
               existing_nullable=False)
    op.alter_column('alarm', 'UpdateTime',
               existing_type=mysql.TIMESTAMP(),
               server_default=sa.text('now()'),
               existing_comment='更新时间',
               existing_nullable=False)
    op.alter_column('algorithm', 'CreateTime',
               existing_type=mysql.DATETIME(),
               server_default=sa.text('now()'),
               existing_comment='创建时间',
               existing_nullable=False)
    op.alter_column('algorithm', 'UpdateTime',
               existing_type=mysql.TIMESTAMP(),
               server_default=sa.text('now()'),
               existing_comment='更新时间',
               existing_nullable=False)
    op.alter_column('areas', 'Latitude',
               existing_type=mysql.DECIMAL(precision=10, scale=2),
               type_=sa.DECIMAL(precision=10, scale=8),
               comment='纬度',
               existing_comment='经度',
               existing_nullable=True)
    op.alter_column('areas', 'Longitude',
               existing_type=mysql.DECIMAL(precision=10, scale=2),
               type_=sa.DECIMAL(precision=11, scale=8),
               comment='经度',
               existing_comment='纬度',
               existing_nullable=True)
    
    op.execute("UPDATE areas SET CreateTime = NOW() WHERE CreateTime IS NULL")

    op.alter_column('areas', 'CreateTime',
               existing_type=sa.DATETIME(),
               nullable=False,
               server_default=sa.text("now()"),
               comment='创建时间')
    
    op.execute("UPDATE areas SET UpdateTime = NOW() WHERE UpdateTime IS NULL")
    op.alter_column('areas', 'UpdateTime',
               existing_type=mysql.TIMESTAMP(),
               server_default=sa.text('now()'),
               nullable=False,
               existing_comment='更新时间')
    op.alter_column('areas', 'DeadTime',
               existing_type=mysql.TIMESTAMP(),
               comment='失效时间(软删除标记)',
               existing_comment='删除时间',
               existing_nullable=True)
    op.create_index(op.f('ix_areas_AreaId'), 'areas', ['AreaId'], unique=False)
    op.create_unique_constraint(None, 'areas', ['AreaName'])
    op.create_table_comment(
        'areas',
        '区域信息表',
        existing_comment=None,
        schema=None
    )
    op.alter_column('device', 'CreateTime',
               existing_type=mysql.DATETIME(),
               server_default=sa.text('now()'),
               existing_comment='创建时间',
               existing_nullable=False)
    op.alter_column('device', 'UpdateTime',
               existing_type=mysql.TIMESTAMP(),
               server_default=sa.text('now()'),
               existing_comment='更新时间',
               existing_nullable=False)
    op.create_unique_constraint(None, 'device', ['BoardIp'])
    op.alter_column('device_face_mapping', 'CreateTime',
               existing_type=mysql.DATETIME(),
               server_default=sa.text('now()'),
               nullable=False,
               existing_comment='创建时间')
    op.alter_column('device_face_mapping', 'UpdateTime',
               existing_type=mysql.TIMESTAMP(),
               server_default=sa.text('now()'),
               nullable=False,
               existing_comment='更新时间')
    op.alter_column('device_face_mapping', 'DeadTime',
               existing_type=mysql.DATETIME(),
               type_=sa.TIMESTAMP(),
               existing_comment='失效时间(软删除标记)',
               existing_nullable=True)
    op.alter_column('ef_alarm', 'FenceState',
               existing_type=mysql.VARCHAR(length=255),
               type_=sa.String(length=10),
               existing_comment='报警状态',
               existing_nullable=True)
    op.alter_column('ef_alarm', 'CreateTime',
               existing_type=mysql.DATETIME(),
               server_default=sa.text('now()'),
               existing_comment='创建时间',
               existing_nullable=False)
    op.alter_column('ef_alarm', 'UpdateTime',
               existing_type=mysql.DATETIME(),
               server_default=sa.text('now()'),
               type_=sa.TIMESTAMP(),
               nullable=False,
               existing_comment='更新时间')
    op.alter_column('ef_alarm', 'DeadTime',
               existing_type=mysql.DATETIME(),
               type_=sa.TIMESTAMP(),
               comment='失效时间(软删除标记)',
               existing_comment='逻辑删除时间',
               existing_nullable=True)
    op.create_table_comment(
        'ef_alarm',
        '电子围栏报警表',
        existing_comment=None,
        schema=None
    )
    op.alter_column('elect_fence', 'CreateTime',
               existing_type=mysql.DATETIME(),
               server_default=sa.text('now()'),
               existing_comment='创建时间',
               existing_nullable=False)
    op.alter_column('elect_fence', 'UpdateTime',
               existing_type=mysql.TIMESTAMP(),
               server_default=sa.text('now()'),
               nullable=False,
               existing_comment='更新时间')
    op.alter_column('elect_fence', 'DeadTime',
               existing_type=mysql.TIMESTAMP(),
               comment='失效时间(软删除标记)',
               existing_comment='删除时间',
               existing_nullable=True)
    op.create_unique_constraint(None, 'elect_fence', ['FenceName'])
    op.create_table_comment(
        'elect_fence',
        '电子围栏表',
        existing_comment=None,
        schema=None
    )
    op.alter_column('face', 'RegisterTime',
               existing_type=mysql.DATETIME(),
               server_default=sa.text('now()'),
               existing_comment='注册日期',
               existing_nullable=False)
    op.alter_column('face', 'BirthYear',
               existing_type=mysql.INTEGER(),
               nullable=True,
               existing_comment='出生年份')
    op.alter_column('face', 'Gender',
               existing_type=mysql.VARCHAR(length=10),
               nullable=True,
               existing_comment='性别')
    op.alter_column('face', 'CreateTime',
               existing_type=mysql.DATETIME(),
               server_default=sa.text('now()'),
               existing_comment='创建时间',
               existing_nullable=False)
    op.alter_column('face', 'UpdateTime',
               existing_type=mysql.TIMESTAMP(),
               server_default=sa.text('now()'),
               existing_comment='更新时间',
               existing_nullable=False)
    op.alter_column('face', 'DeadTime',
               existing_type=mysql.DATETIME(),
               type_=sa.TIMESTAMP(),
               comment='失效时间(软删除标记)',
               existing_comment='失效时间',
               existing_nullable=True)
    op.alter_column('facelibrary', 'CreateTime',
               existing_type=mysql.DATETIME(),
               server_default=sa.text('now()'),
               existing_comment='创建时间',
               existing_nullable=False)
    op.alter_column('facelibrary', 'UpdateTime',
               existing_type=mysql.TIMESTAMP(),
               server_default=sa.text('now()'),
               existing_comment='更新时间',
               existing_nullable=False)
    op.alter_column('facelibrary', 'DeadTime',
               existing_type=mysql.DATETIME(),
               type_=sa.TIMESTAMP(),
               comment='失效时间(软删除标记)',
               existing_comment='失效时间',
               existing_nullable=True)
    op.alter_column('parameter', 'CreateTime',
               existing_type=mysql.DATETIME(),
               server_default=sa.text('now()'),
               existing_comment='创建时间',
               existing_nullable=False)
    op.alter_column('parameter', 'UpdateTime',
               existing_type=mysql.TIMESTAMP(),
               server_default=sa.text('now()'),
               existing_comment='更新时间',
               existing_nullable=False)
    op.add_column('passage', sa.Column('MediaIp', sa.String(length=20), nullable=False, comment='nvr或摄像头ip'))
    op.add_column('passage', sa.Column('MediaPort', sa.Integer(), nullable=False, comment='nvr或摄像头端口'))
    op.add_column('passage', sa.Column('MediaUser', sa.String(length=20), nullable=False, comment='nvr或摄像头用户名'))
    op.add_column('passage', sa.Column('MediaPassword', sa.String(length=20), nullable=False, comment='nvr或摄像头密码'))
    op.add_column('passage', sa.Column('MediaNum', sa.String(length=20), nullable=False, comment='通道号'))
    
    op.execute("UPDATE passage SET BoardIp = '' WHERE BoardIp IS NULL")
    op.alter_column('passage', 'BoardIp',
               existing_type=mysql.VARCHAR(length=255),
               nullable=False,
               comment='设备IP地址')
    op.alter_column('passage', 'AreaId',
               existing_type=mysql.INTEGER(),
               nullable=False,
               comment='区域ID')
    op.alter_column('passage', 'CreateTime',
               existing_type=mysql.DATETIME(),
               server_default=sa.text('now()'),
               nullable=False,
               existing_comment='创建时间')
    op.alter_column('passage', 'UpdateTime',
               existing_type=mysql.TIMESTAMP(),
               server_default=sa.text('now()'),
               nullable=False,
               existing_comment='更新时间')
    op.drop_column('passage', 'MediaUrl')
    op.alter_column('task', 'CreateTime',
               existing_type=mysql.DATETIME(),
               server_default=sa.text('now()'),
               existing_comment='创建时间',
               existing_nullable=False)
    op.alter_column('task', 'UpdateTime',
               existing_type=mysql.TIMESTAMP(),
               server_default=sa.text('now()'),
               existing_comment='更新时间',
               existing_nullable=False)
    op.alter_column('user', 'CreateTime',
               existing_type=mysql.DATETIME(),
               server_default=sa.text('now()'),
               existing_comment='创建时间',
               existing_nullable=False)
    op.alter_column('user', 'UpdateTime',
               existing_type=mysql.TIMESTAMP(),
               server_default=sa.text('now()'),
               existing_comment='更新时间',
               existing_nullable=False)
    # ### end Alembic commands ###


def downgrade() -> None:
    """Downgrade schema."""
    # ### commands auto generated by Alembic - please adjust! ###
    op.alter_column('user', 'UpdateTime',
               existing_type=mysql.TIMESTAMP(),
               server_default=sa.text('CURRENT_TIMESTAMP'),
               existing_comment='更新时间',
               existing_nullable=False)
    op.alter_column('user', 'CreateTime',
               existing_type=mysql.DATETIME(),
               server_default=sa.text('CURRENT_TIMESTAMP'),
               existing_comment='创建时间',
               existing_nullable=False)
    op.alter_column('task', 'UpdateTime',
               existing_type=mysql.TIMESTAMP(),
               server_default=sa.text('CURRENT_TIMESTAMP'),
               existing_comment='更新时间',
               existing_nullable=False)
    op.alter_column('task', 'CreateTime',
               existing_type=mysql.DATETIME(),
               server_default=sa.text('CURRENT_TIMESTAMP'),
               existing_comment='创建时间',
               existing_nullable=False)
    op.add_column('passage', sa.Column('MediaUrl', mysql.VARCHAR(length=255), nullable=False, comment='视频流地址'))
    op.alter_column('passage', 'UpdateTime',
               existing_type=mysql.TIMESTAMP(),
               server_default=sa.text('CURRENT_TIMESTAMP'),
               nullable=True,
               existing_comment='更新时间')
    op.alter_column('passage', 'CreateTime',
               existing_type=mysql.DATETIME(),
               server_default=sa.text('CURRENT_TIMESTAMP'),
               nullable=True,
               existing_comment='创建时间')
    op.alter_column('passage', 'AreaId',
               existing_type=mysql.INTEGER(),
               nullable=True,
               comment=None,
               existing_comment='区域ID')
    op.alter_column('passage', 'BoardIp',
               existing_type=mysql.VARCHAR(length=255),
               nullable=True,
               comment=None,
               existing_comment='设备IP地址')
    op.drop_column('passage', 'MediaNum')
    op.drop_column('passage', 'MediaPassword')
    op.drop_column('passage', 'MediaUser')
    op.drop_column('passage', 'MediaPort')
    op.drop_column('passage', 'MediaIp')
    op.alter_column('parameter', 'UpdateTime',
               existing_type=mysql.TIMESTAMP(),
               server_default=sa.text('CURRENT_TIMESTAMP'),
               existing_comment='更新时间',
               existing_nullable=False)
    op.alter_column('parameter', 'CreateTime',
               existing_type=mysql.DATETIME(),
               server_default=sa.text('CURRENT_TIMESTAMP'),
               existing_comment='创建时间',
               existing_nullable=False)
    op.alter_column('facelibrary', 'DeadTime',
               existing_type=sa.TIMESTAMP(),
               type_=mysql.DATETIME(),
               comment='失效时间',
               existing_comment='失效时间(软删除标记)',
               existing_nullable=True)
    op.alter_column('facelibrary', 'UpdateTime',
               existing_type=mysql.TIMESTAMP(),
               server_default=sa.text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'),
               existing_comment='更新时间',
               existing_nullable=False)
    op.alter_column('facelibrary', 'CreateTime',
               existing_type=mysql.DATETIME(),
               server_default=sa.text('CURRENT_TIMESTAMP'),
               existing_comment='创建时间',
               existing_nullable=False)
    op.alter_column('face', 'DeadTime',
               existing_type=sa.TIMESTAMP(),
               type_=mysql.DATETIME(),
               comment='失效时间',
               existing_comment='失效时间(软删除标记)',
               existing_nullable=True)
    op.alter_column('face', 'UpdateTime',
               existing_type=mysql.TIMESTAMP(),
               server_default=sa.text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'),
               existing_comment='更新时间',
               existing_nullable=False)
    op.alter_column('face', 'CreateTime',
               existing_type=mysql.DATETIME(),
               server_default=sa.text('CURRENT_TIMESTAMP'),
               existing_comment='创建时间',
               existing_nullable=False)
    op.alter_column('face', 'Gender',
               existing_type=mysql.VARCHAR(length=10),
               nullable=False,
               existing_comment='性别')
    op.alter_column('face', 'BirthYear',
               existing_type=mysql.INTEGER(),
               nullable=False,
               existing_comment='出生年份')
    op.alter_column('face', 'RegisterTime',
               existing_type=mysql.DATETIME(),
               server_default=sa.text('CURRENT_TIMESTAMP'),
               existing_comment='注册日期',
               existing_nullable=False)
    op.drop_table_comment(
        'elect_fence',
        existing_comment='电子围栏表',
        schema=None
    )
    op.drop_constraint(None, 'elect_fence', type_='unique')
    op.alter_column('elect_fence', 'DeadTime',
               existing_type=mysql.TIMESTAMP(),
               comment='删除时间',
               existing_comment='失效时间(软删除标记)',
               existing_nullable=True)
    op.alter_column('elect_fence', 'UpdateTime',
               existing_type=mysql.TIMESTAMP(),
               server_default=None,
               nullable=True,
               existing_comment='更新时间')
    op.alter_column('elect_fence', 'CreateTime',
               existing_type=mysql.DATETIME(),
               server_default=None,
               existing_comment='创建时间',
               existing_nullable=False)
    op.drop_table_comment(
        'ef_alarm',
        existing_comment='电子围栏报警表',
        schema=None
    )
    op.alter_column('ef_alarm', 'DeadTime',
               existing_type=sa.TIMESTAMP(),
               type_=mysql.DATETIME(),
               comment='逻辑删除时间',
               existing_comment='失效时间(软删除标记)',
               existing_nullable=True)
    op.alter_column('ef_alarm', 'UpdateTime',
               existing_type=sa.TIMESTAMP(),
               server_default=None,
               type_=mysql.DATETIME(),
               nullable=True,
               existing_comment='更新时间')
    op.alter_column('ef_alarm', 'CreateTime',
               existing_type=mysql.DATETIME(),
               server_default=None,
               existing_comment='创建时间',
               existing_nullable=False)
    op.alter_column('ef_alarm', 'FenceState',
               existing_type=sa.String(length=10),
               type_=mysql.VARCHAR(length=255),
               existing_comment='报警状态',
               existing_nullable=True)
    op.alter_column('device_face_mapping', 'DeadTime',
               existing_type=sa.TIMESTAMP(),
               type_=mysql.DATETIME(),
               existing_comment='失效时间(软删除标记)',
               existing_nullable=True)
    op.alter_column('device_face_mapping', 'UpdateTime',
               existing_type=mysql.TIMESTAMP(),
               server_default=sa.text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'),
               nullable=True,
               existing_comment='更新时间')
    op.alter_column('device_face_mapping', 'CreateTime',
               existing_type=mysql.DATETIME(),
               server_default=sa.text('CURRENT_TIMESTAMP'),
               nullable=True,
               existing_comment='创建时间')
    op.drop_constraint(None, 'device', type_='unique')
    op.alter_column('device', 'UpdateTime',
               existing_type=mysql.TIMESTAMP(),
               server_default=sa.text('CURRENT_TIMESTAMP'),
               existing_comment='更新时间',
               existing_nullable=False)
    op.alter_column('device', 'CreateTime',
               existing_type=mysql.DATETIME(),
               server_default=sa.text('CURRENT_TIMESTAMP'),
               existing_comment='创建时间',
               existing_nullable=False)
    op.drop_table_comment(
        'areas',
        existing_comment='区域信息表',
        schema=None
    )
    op.drop_constraint(None, 'areas', type_='unique')
    op.drop_index(op.f('ix_areas_AreaId'), table_name='areas')
    op.alter_column('areas', 'DeadTime',
               existing_type=mysql.TIMESTAMP(),
               comment='删除时间',
               existing_comment='失效时间(软删除标记)',
               existing_nullable=True)
    op.alter_column('areas', 'UpdateTime',
               existing_type=mysql.TIMESTAMP(),
               server_default=None,
               nullable=True,
               existing_comment='更新时间')
    op.alter_column('areas', 'CreateTime',
               existing_type=mysql.DATETIME(),
               server_default=None,
               nullable=True,
               existing_comment='创建时间')
    op.alter_column('areas', 'Longitude',
               existing_type=sa.DECIMAL(precision=11, scale=8),
               type_=mysql.DECIMAL(precision=10, scale=2),
               comment='纬度',
               existing_comment='经度',
               existing_nullable=True)
    op.alter_column('areas', 'Latitude',
               existing_type=sa.DECIMAL(precision=10, scale=8),
               type_=mysql.DECIMAL(precision=10, scale=2),
               comment='经度',
               existing_comment='纬度',
               existing_nullable=True)
    op.alter_column('algorithm', 'UpdateTime',
               existing_type=mysql.TIMESTAMP(),
               server_default=sa.text('CURRENT_TIMESTAMP'),
               existing_comment='更新时间',
               existing_nullable=False)
    op.alter_column('algorithm', 'CreateTime',
               existing_type=mysql.DATETIME(),
               server_default=sa.text('CURRENT_TIMESTAMP'),
               existing_comment='创建时间',
               existing_nullable=False)
    op.alter_column('alarm', 'UpdateTime',
               existing_type=mysql.TIMESTAMP(),
               server_default=sa.text('CURRENT_TIMESTAMP'),
               existing_comment='更新时间',
               existing_nullable=False)
    op.alter_column('alarm', 'CreateTime',
               existing_type=mysql.DATETIME(),
               server_default=sa.text('CURRENT_TIMESTAMP'),
               existing_comment='创建时间',
               existing_nullable=False)
    # ### end Alembic commands ###
